﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;

namespace EffiProzDBTest
{      

    /// <summary>
    /// Summary description for BasicTableTest
    /// </summary>
    [TestClass]
    public class BasicTableTest : BaseTest
    {
    


        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }


        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion


        //[TestMethod]
        //public void ServerTestCreateTable()
        //{

        //    Result resultIn;
        //    Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

        //    SessionInterface sessionProxy = new HSQLClientConnection("localhost", 9001, "", "testdb", false, "sa", "");

        //    string table = TestHelper.GetNewTableName();
        //    string sql = "CREATE CACHED TABLE " + table + " (col1 int, col2 double, \"col7\" char(60),col4 varchar(32));";

        //    resultOut.setMainString(sql);
        //    resultOut.setMaxRows(1000);

        //    resultIn = sessionProxy.execute(resultOut);

        //    Assert.IsNotNull(resultIn);
        //    Assert.IsFalse(resultIn.isError());
        //    Assert.IsFalse(resultIn.isData());

        //    sql = "SELECT * FROM " + table + ";";

        //    resultOut.setMainString(sql);
        //    resultOut.setMaxRows(1000);

        //    resultIn = sessionProxy.execute(resultOut);

        //    Assert.IsNotNull(resultIn);
        //    Assert.IsFalse(resultIn.isError());
        //    Assert.IsTrue(resultIn.isData());

        //    sql = "DROP TABLE " + table + ";";

        //    resultOut.setMainString(sql);
        //    resultOut.setMaxRows(1000);

        //    resultIn = sessionProxy.execute(resultOut);

        //    Assert.IsNotNull(resultIn);
        //    Assert.IsFalse(resultIn.isError());
        //    Assert.IsFalse(resultIn.isData());

        //    sql = "SELECT * FROM " + table + ";";

        //    resultOut.setMainString(sql);
        //    resultOut.setMaxRows(1000);

        //    resultIn = sessionProxy.execute(resultOut);

        //    Assert.IsNotNull(resultIn);
        //    Assert.IsTrue(resultIn.isError());


        //}
        
        
        
        
        [TestMethod]
        public void TestCreateTable()
        {

            Result resultIn;
            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

            string table =TestHelper.GetNewTableName();
            string sql = "CREATE CACHED TABLE " + table + " (col1 int, col2 double, \"col7\" char(60),col4 varchar(32));";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            sql = "SELECT * FROM " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsTrue(resultIn.isData());

            sql = "DROP TABLE " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            sql = "SELECT * FROM " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsTrue(resultIn.isError());
   
           
        }

        [TestMethod]
        public void TestTableInsert()
        {

            Result resultIn;
            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

            string table = "tablei2";
            string sql = "CREATE TABLE " + table + " (col1 int, col2 varchar(32));";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            int col1val = 111;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES("+col1val+",'" + col2val + "');";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());
            Assert.AreEqual(resultIn.updateCount, 1);

            sql = "SELECT * FROM " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsTrue(resultIn.isData());

            Assert.AreEqual(resultIn.getSize(), 1);

            Iterator it = resultIn.iterator();
            object[] row = (object[])it.next();

            Assert.AreEqual(row[0], col1val);
            Assert.AreEqual(row[1], col2val);            
        }


        [TestMethod]
        public void TestTableMultiInsert()
        {

            Result resultIn;
            Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

            string table = "tableim2";
            string sql = "CREATE TABLE " + table + " (col1 int, col2 varchar(32));";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());

            int[] col1val = new int[5];
            string[] col2val = new string[5];
            sql = "";
            for (int i = 0; i < col1val.Length; i++)
            {
                col1val[i] = 1002 + i;
                col2val[i] = "Hello" + i;
                sql += " INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val[i] + ",'" + col2val[i] + "'); ";
            }

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsFalse(resultIn.isData());
            Assert.IsTrue(resultIn.isUpdateCount());
           // Assert.AreEqual(col1val.Length, resultIn.getUpdateCount());

            sql = "SELECT * FROM " + table + ";";

            resultOut.setMainString(sql);
            resultOut.setMaxRows(1000);

            resultIn = sessionProxy.execute(resultOut);

            Assert.IsNotNull(resultIn);
            Assert.IsFalse(resultIn.isError());
            Assert.IsTrue(resultIn.isData());
            Assert.IsFalse(resultIn.isUpdateCount());
             Assert.AreEqual(col1val.Length ,resultIn.getSize());

            Iterator it = resultIn.iterator();
            for(int j =0; j< col1val.Length; j++){
              object[] row = (object[])it.next();
              Assert.AreEqual(col1val[j],row[0]);
              Assert.AreEqual(col2val[j], row[1]);
            }
          
        }

   
        [TestMethod]
        public void TestPrimaryKey()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int PRIMARY KEY, col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

          
            int col1val = 111;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

             col1val = 112;
             col2val = "Hoox";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');"; // none vialation

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            col1val = 111;
            col2val = "Hoof";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES(" + col1val + ",'" + col2val + "');"; // primary key vialation should fail

            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);
           
        }

        [TestMethod]
        public void TestDefaultValue()
        {
            //test  default value insert to default value column
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int DEFAULT 1021, col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 1021;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            //test non default value insert to default value column

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            table = TestHelper.GetNewTableName();
            sql = "CREATE TABLE " + table + " (col1 int DEFAULT 1021, col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


             col1val = 500;
             col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1, col2)  VALUES("+col1val+", '" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

          
        }

        [TestMethod]
        public void TestDefaultAutoGen()
        {
            //test  default auto gen with no options
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int GENERATED BY DEFAULT AS IDENTITY, col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);          

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            col1val = 1;
            col2val = "Hoo2";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

            TestHelper.MatchSingleResultRow(resultIn,1, col1val, col2val);          

        }

        [TestMethod]
        public void TestDefaultAutoGenWithStartValue()
        {
            //test  default auto gen with no options
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int GENERATED BY DEFAULT AS IDENTITY (START WITH 113), col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 113;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            col1val = 114;
            col2val = "Hoor";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);      

        }

        [TestMethod]
        public void TestDefaultAutoGenWithStartStepValue()
        {
            //test  default auto gen with no options
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int GENERATED BY DEFAULT AS IDENTITY (START WITH 113, INCREMENT BY 3), col2 varchar(32));";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 113;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);


            col1val = 116;
            col2val = "Hoor";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);
        }


        [TestMethod]
        public void TestNull()
        {
    
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32));"; // allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int? col1val = null;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql); 

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);
          
        }

        [TestMethod]
        public void TestExplicitNull()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int NULL, col2 varchar(32));"; // allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int? col1val = null;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }

        [TestMethod]
        public void TestNotNull()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int NOT NULL, col2 varchar(32));"; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 55;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            col1val = 55;
            col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1 , col2)  VALUES("+col1val+" ,'" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }

        [TestMethod]
        public void TestIdentityColumn()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int IDENTITY, col2 varchar(32));"; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col2)  VALUES('" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

            col1val = 55;
            col2val = "Hoo2";
            sql = "INSERT INTO " + table + " (col1 , col2)  VALUES(" + col1val + " ,'" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql); 

         

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);

            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);       

        }

        [TestMethod]
        public void TestExplicitNullValue()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int IDENTITY, col2 varchar(32));"; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1 , col2)  VALUES(NULL, '" + col2val + "');";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);         

        }




        [TestMethod]
        public void TestInsertWithSelect()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int ,  col2 int );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 3;
            string rcol2val = "test1";
            int rcol3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

         
            sql = "INSERT INTO "+table2+" SELECT col1,col3 FROM "+table +" ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table2, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val,  col3val);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, rcol3val);

        }


        [TestMethod]
        public void TestDropTable()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 3;
            string rcol2val = "test1";
            int rcol3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int ,  col2 int );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            //drop none existing table
            sql = "DROP TABLE dummy;";
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = "DROP TABLE dummy IF EXISTS;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            //drop existing table
            sql = "DROP TABLE "+table2+ " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table2, 2);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql);
           
            //drop table with data

            sql = "DROP TABLE " + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql);

        }

        [TestMethod]
        public void TestIdentityFunction()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int IDENTITY, col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            int col1val = 0;
            string col2val = "Hoo";
            sql = "INSERT INTO " + table + " (col1 , col2)  VALUES(NULL, '" + col2val + "'); CALL IDENTITY();";

            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql,1);
            object[] objs = (object[])resultIn.iterator().next();
            col1val = (int)objs[0];

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            TestHelper.MatchSingleResult(resultIn, col1val, col2val);

        }



        [TestMethod]
        public void AlterTableRenameColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col1 RENAME TO colx;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql);

            sql = "SELECT colx, col2 FROM " + table + " ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResult(resultIn, col1val, col2val);
        }

        [TestMethod]
        public void AlterTableSetDefaultColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col2 SET DEFAULT 'TestDef';";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, "TestDef");
        }

        [TestMethod]
        public void AlterTableSetNullColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col2 SET NOT NULL;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 2, col1val,col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
           
        }

        [TestMethod]
        public void AlterTableDropColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";
            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " DROP COLUMN col2 ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);
      

        }

        [TestMethod]
        public void AlterTableAddColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " Add COLUMN col3 int ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col3val = 897;
            sql = TestHelper.CreateStdInsert(table, 3, col1val,col2val,col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = "SELECT * FROM " + table;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val, col3val);

        }

        [TestMethod]
        public void AlterTableAddBeforeColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            sql = "ALTER TABLE " + table + " Add COLUMN col3 int BEFORE col1;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col3val = 897;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = "SELECT * FROM "+table;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col3val,col1val, col2val);

        }

        [TestMethod]
        public void AlterTableRenameTable()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "Hoo";

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

            string newTable = TestHelper.GetNewTableName();
            sql = "ALTER TABLE " + table + " RENAME TO "+newTable+ " ;" ;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(newTable, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0,  col1val, col2val);
        }

        [TestMethod]
        public void AlterTableRestartColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int IDENTITY); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            int col2val = 0;

            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col2 RESTART WITH 1000 ;" ;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            col2val = 1000;
            sql = TestHelper.CreateStdInsert(table, 1, col1val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val);
        }


        [TestMethod]
        public void AlterTableChangeDefinitionColumn()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "ALTER TABLE " + table + " ALTER COLUMN col2 BIGINT ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 77;
            int rcol2val = 6666;
            sql = TestHelper.CreateStdInsert(table, 2, rcol1val,rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, (long)col2val);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, (long)rcol2val);
        }

        [TestMethod]
        public void SetReadOnly()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SET READONLY TRUE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 77;
            int rcol2val = 6666;
            sql = TestHelper.CreateStdInsert(table, 2, rcol1val, rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SET READONLY FALSE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

   
            sql = TestHelper.CreateStdInsert(table, 2, rcol1val, rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, rcol2val);
        
        }


        [TestMethod]
        public void SetIgnoreCase()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32)); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            string col2val = "IranTHa";

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT col1, col2 FROM "+table +" WHERE col2='"+col2val+"' ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);
            
            //all lower case select
            
            sql = "SELECT col1, col2 FROM " + table + " WHERE col2='" + col2val.ToLower() + "' ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);

            sql = "SET IGNORECASE TRUE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            sql = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32)); "; // not allow null for col1
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table2, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT col1, col2 FROM " + table2 + " WHERE col2='" + col2val.ToLower() + "' ;"; // success
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SELECT col1, col2 FROM " + table2 + " WHERE col2='" + col2val.ToUpper() + "' ;"; // success
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            // Test Ignore case false

            sql = "SET IGNORECASE FALSE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table3 = TestHelper.GetNewTableName();
            sql = "CREATE TABLE " + table3 + " (col1 int , col2 varchar(32)); "; // not allow null for col1
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table3, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT col1, col2 FROM " + table3 + " WHERE col2='" + col2val + "' ;"; // success
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SELECT col1, col2 FROM " + table3 + " WHERE col2='" + col2val.ToUpper() + "' ;"; // failure
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0); 
          
        }

        [TestMethod]
        public void ReadOnlyTables()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 int); "; // not allow null for col1

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 67;
            int col2val = 5;

            sql = TestHelper.CreateStdInsert(table, 2, col1val, col2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);

            sql = "SET TABLE " + table + " READONLY TRUE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 77;
            int rcol2val = 6666;
            sql = TestHelper.CreateStdInsert(table, 2, rcol1val, rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = "SET TABLE " + table + " READONLY FALSE; ";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table, 2, rcol1val, rcol2val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, rcol2val);
         
        }     
      
    }
}
